package com.feib.soeasy.dao.hibernate;

import java.math.BigDecimal;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Repository;

import com.feib.soeasy.dao.CommissionCodeTableDao;
import com.feib.soeasy.model.CommissionCodeTable;
import com.feib.soeasy.model.Group;

@Repository("commissionCodeTableDao")
public class CommissionCodeTableHibernateDao extends BaseHibernateDao<CommissionCodeTable, Long> implements CommissionCodeTableDao {

    @Override
    public List<CommissionCodeTable> queryCommissionCodeTables(
                    String qryCode, String qryCodeName) {
        Criteria criteria = getSession().createCriteria(CommissionCodeTable.class);
        if(null != qryCode && !"".equals(qryCode))
            criteria.add(Restrictions.eq("commissionCode", qryCode));

        if(null != qryCodeName && !"".equals(qryCodeName))
            criteria.add(Restrictions.like("commissionCodeName", qryCodeName, MatchMode.ANYWHERE));
      
        criteria.addOrder(Order.asc("commissionCode"));
        return criteria.list();
    }

    @Override
    public CommissionCodeTable save(CommissionCodeTable commissionCodeTable) {
        getSession().saveOrUpdate(commissionCodeTable);
        return commissionCodeTable;
    }
    
    @Override
    public CommissionCodeTable getCommissionCodeTable(Group group, String commissionCode)
    {
    	StringBuffer sql = new StringBuffer();
    	sql.append("select c.commission_type , c.commission_amount , c.bank_amount , c.payer_amount, c.group_amount from commission_code_table c ");
    	sql.append("where  c.commission_code_pk in ( ");
    	sql.append("  select  fdc.commission_code_pk from format_def_commission fdc where fdc.format_def_pk in (");
    	sql.append("      select fd.format_def_pk from format_definition fd where fd.group_pk = :groupPk ");
    	sql.append("  ) ");
    	sql.append(") ");
    	sql.append("and commission_code = :commissionCode ");

    	Query query = this.getSession().createSQLQuery(sql.toString());
    	query.setParameter("groupPk", group.getGroupPk());
    	query.setParameter("commissionCode", commissionCode);
    	
    	List list = query.list();
    	if(list != null && list.size() > 0)
    	{
    		Object[] obj = (Object[])list.get(0);
    		String commissionType = (String)obj[0];
    		BigDecimal commissionAmount = (BigDecimal)obj[1];
    		BigDecimal bankAmount = (BigDecimal)obj[2];
    		BigDecimal payerAmount = (BigDecimal)obj[3];
    		BigDecimal groupAmount = (BigDecimal)obj[4];
    		
    		CommissionCodeTable commissionCodeTable = new CommissionCodeTable();
    		commissionCodeTable.setCommissionType(commissionType);
    		commissionCodeTable.setBankAmount(new Long(bankAmount.longValue()));
    		commissionCodeTable.setCommissionAmount(new Long(commissionAmount.longValue()));
    		commissionCodeTable.setPayerAmount(new Long(payerAmount.longValue()));
    		commissionCodeTable.setGroupAmount(new Long(groupAmount.longValue()));
    		
    		return commissionCodeTable;
    	}
    	
    	
    	return null;
    }
    
    
    @Override
    public CommissionCodeTable getCommissionCodeTable(String commissionCode)
    {
    	StringBuffer sql = new StringBuffer();
    	sql.append("select commission_type , commission_amount , bank_amount , payer_amount, group_amount from commission_code_table ");
    	sql.append("where  commission_code = :commissionCode ");
    	
    	Query query = this.getSession().createSQLQuery(sql.toString());    	
    	query.setParameter("commissionCode", commissionCode);
    	
    	List list = query.list();
    	if(list != null && list.size() > 0)
    	{
    		Object[] obj = (Object[])list.get(0);
    		String commissionType = (String)obj[0];
    		BigDecimal commissionAmount = (BigDecimal)obj[1];
    		BigDecimal bankAmount = (BigDecimal)obj[2];
    		BigDecimal payerAmount = (BigDecimal)obj[3];
    		BigDecimal groupAmount = (BigDecimal)obj[4];
    		
    		CommissionCodeTable commissionCodeTable = new CommissionCodeTable();
    		commissionCodeTable.setCommissionType(commissionType);
    		commissionCodeTable.setBankAmount(new Long(bankAmount.longValue()));
    		commissionCodeTable.setCommissionAmount(new Long(commissionAmount.longValue()));
    		commissionCodeTable.setPayerAmount(new Long(payerAmount.longValue()));
    		commissionCodeTable.setGroupAmount(new Long(groupAmount.longValue()));
    		
    		return commissionCodeTable;
    	}
    	
    	
    	return null;
    }

}
